from flask import Flask, render_template, request, redirect, url_for, session, Blueprint
from .utils.auth import query
bp = Blueprint('audit', __name__, url_prefix='/audit')


@bp.route('/', methods=['GET', 'POST'])
def audit():
    if request.method == 'GET':
        sql = "select * from bills"
        result = query(sql)
        return render_template('audit/audit_index.html', result=result)


@bp.route('/audit_search', methods=['GET', 'POST'])
def audit_search():
    if request.method == 'POST':
        billno = request.form.get('billno')
        user = request.form.get('user')
        min_amount = request.form.get('min-amount')
        max_amount = request.form.get('max-amount')
        reason = request.form.get('reason')
        begin_time = request.form.get('begin-time')
        end_time = request.form.get('end-time')

        sql = "select * from bills where 1 = 1"
        if len(billno) != 0:
            sql += " and billno = '%s'" % billno
        if len(user) != 0:
            sql += " and user = '%s'" % user
        if len(min_amount) != 0:
            sql += " and amount >= '%s'" % min_amount
        if len(max_amount) != 0:
            sql += " and amount <= '%s'" % max_amount
        if len(reason) != 0:
            sql += " and reason = '%s'" % reason
        if len(begin_time) != 0:
            sql += " and time >= '%s'" % begin_time
        if len(end_time) != 0:
            sql += " and time <= '%s'" % end_time

        sort1 = request.form.get('sort1')
        sort2 = request.form.get('sort2')
        sort3 = request.form.get('sort3')
        order1 = request.form.get('order1')
        order2 = request.form.get('order2')
        order3 = request.form.get('order3')

        asc_list = []
        desc_list = []
        if sort1 != "none":
            if order1 == "ASC":
                asc_list.append(sort1)
            else:
                desc_list.append(sort1)
        if sort2 != "none":
            if sort2 not in asc_list and sort2 not in desc_list:
                if order2 == "ASC":
                    asc_list.append(sort2)
                else:
                    desc_list.append(sort2)
        if sort3 != "none":
            if sort3 not in asc_list and sort3 not in desc_list:
                if order3 == "ASC":
                    asc_list.append(sort3)
                else:
                    desc_list.append(sort3)
        if len(asc_list)!=0 or len(desc_list)!=0:
            sql+=" order by "
            if sort1 in asc_list:
                sql += " %s asc" % sort1
            else:
                sql += " %s desc" % sort1
            if sort2 != "none":
                if sort2 in asc_list:
                    sql += ", %s asc" % sort2
                else:
                    sql += ", %s desc" % sort2
            if sort3 != "none":
                if sort3 in asc_list:
                    sql += ", %s asc" % sort3
                else:
                    sql += ", %s desc" % sort3
        print(sql)
        result = query(sql)
        print(result)
        if not result:
            return redirect(url_for('search_failed'))
        else:
            # sql = "delete " + sql[8:]
            # query(sql)
            return render_template('audit/audit_index.html', result=result)
    else:
        return render_template('audit/audit_search.html')
